﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Data.OleDb;
using RD.Metadata.Database.Sql;

namespace SmartLib
{

    /// <summary>
    /// Summary description for Helper
    /// </summary>
    public class Helper
    {
        public Helper()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public static SqlConnection taoketnoi()
        {
            string chuoi = "Data Source=C6E9961739FB407;Initial Catalog=QLMT;Integrated Security=True";
            SqlConnection con = new SqlConnection(chuoi);
            con.Open();
            return con;
        }

        public static DataTable GetDataTable(string strSQL)
        {
            SqlConnection con = taoketnoi(); 
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            finally
            {
                //'finally' block is ALWAYS get called.
                con.Close();
                con.Dispose();
            } 
        
           /*  String ConnectionString;
            OleDbConnection MyOleDbConnection = new OleDbConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();
            MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query, MyOleDbConnection);

            DataTable myDataTable = new DataTable();

            MyOleDbConnection.Open(); 
            try { MyOleDbDataAdapter.Fill(myDataTable); }
            finally { MyOleDbConnection.Close(); }

            return myDataTable;*/
        }
       
        public static string getUploadLogoFolder()
        {
            return ConfigurationSettings.AppSettings["Upload"];
        }
        public static string getConfigValue(string name)
        {
            return ConfigurationSettings.AppSettings[name];

        }

        public static void setConfigValue(string name, string value)
        {
            if (ConfigurationSettings.AppSettings[name] != null)
                ConfigurationSettings.AppSettings[name] = value;
        }
        public static bool checkExist(string TableName, string whereStr, out int errorCode)
        {
            string strSql = "select * "
                            + " from " + TableName
                            + " where " + whereStr;
            SqlConnection myCon = new SqlConnection(getConnectionString());
            SqlCommand myCmd = new SqlCommand(strSql, myCon);
            SqlDataReader drd;
            try
            {
                myCon.Open();
                drd = myCmd.ExecuteReader();
                errorCode = 0;
                if (drd.HasRows == true)
                {
                    drd.Close();
                    myCmd.Connection.Close();
                    myCon.Close();
                    return true;
                }
                drd.Close();
            }
            catch (Exception e)
            {
                e.ToString();
                errorCode = 1;
            }
            myCmd.Connection.Close();
            myCon.Close();
            return false;
        }
        public static string getConnectionString()
        {
            return getConfigValue("ConnectionString");
        }

        /**
         * Des: Thuc thi cau truy van va tra ve doi tuong chua result. Tu dong dong cau noi.
         *		ErrorCode duoc gan bang 1 neu viec kiem tra thanh cong, nguoc lai errorCode se co
         *      gia tri bang 0.
         */
        public static SqlDataReader executeQuery(string strSql, string conStr, out int errorCode)
        {
            SqlDataReader result = null;
            SqlConnection myCon = new SqlConnection(conStr);
            SqlCommand myCmd = new SqlCommand(strSql, myCon);
            try
            {
                myCon.Open();
                result = myCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                errorCode = 1;
            }
            catch (Exception e)
            {
                errorCode = 0;
            }
            return result;
        }

        /**
         * Des: Thuc thi cau truy van va tra ve doi tuong chua result.
         *		ErrorCode duoc gan bang 1 neu viec kiem tra thanh cong, nguoc lai errorCode se co
         *      gia tri bang 0.
         */
        public static SqlDataReader executeQueryNoClose(string strSql, string conStr, out int errorCode)
        {
            SqlDataReader result = null;
            SqlConnection myCon = new SqlConnection(conStr);
            SqlCommand myCmd = new SqlCommand(strSql, myCon);
            try
            {
                myCon.Open();
                result = myCmd.ExecuteReader();
                errorCode = 1;
            }
            catch (Exception e)
            {
                errorCode = 0;
            }
            finally
            {

            }
            return result;
        }

        /**
         * Des: Thuc thi cau truy van dang khong tra ve ket qua (update,...).
         *		Tra ve true neu thuc hien thanh cong, nguoc lai tra ve false.
         */
        public static bool ExecuteNonQuery(string strSql, string conStr)
        {
            SqlConnection myCon = null;
            SqlCommand myCmd = null;
            try
            {
                myCon = new SqlConnection(conStr);
                myCmd = new SqlCommand(strSql, myCon);
                myCon.Open();
                myCmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {

            }
            finally
            {
                myCmd.Connection.Close();
                myCon.Close();
            }
            return true;
        }

        /**
         * Des: Thuc hien danh sach cac lenh khong truy van theo thu tu.
         *		Tra ve true neu tat ca cac lenh deu thanh cong, nguoc lai tra ve false
         *		va cac lenh se duoc rollback neu co the.
         */
        public static bool doTransactionNonQuery(string[] list_of_sql, string conStr)
        {
            SqlConnection myCon = new SqlConnection(conStr);
            myCon.Open();

            // Start a local transaction.
            SqlTransaction myTrans = myCon.BeginTransaction();

            // Enlist the command in the current transaction.
            SqlCommand myCmd = myCon.CreateCommand();
            myCmd.Transaction = myTrans;

            try
            {
                for (int i = 0; i < list_of_sql.Length; i++)
                {
                    myCmd.CommandText = list_of_sql[i];
                    myCmd.ExecuteNonQuery();
                }
                myTrans.Commit();
                return true;
            }
            catch (Exception e)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException ex)
                {
                }
            }
            finally
            {
                myCon.Close();
            }
            return false;
        }

        /**
         * Des: Tra ve so hang ket qua thoa cau truy van.
         *      Neu truy van thuc hien thanh cong errorCode=1, nguoc lai = 0.
         */
        public static int executeQuery(string sql, out int errorCode)
        {
            int result = 0;
            SqlConnection myCon = new SqlConnection(getConnectionString());
            SqlCommand myCmd = new SqlCommand(sql, myCon);
            try
            {
                myCon.Open();
                result = myCmd.ExecuteNonQuery();
                errorCode = 1;
            }
            catch (Exception e)
            {
                errorCode = 0;
            }

            myCmd.Connection.Close();
            myCon.Close();
            return result;
        }

        /**
         * Des: Tra ve doi tuong DataSet chua du lieu.
         *      Neu truy van thuc hien thanh cong errorCode=1, nguoc lai = 0.
         */
        public static DataSet getDataSet(string strSql, string conStr, out int errorCode)
        {
            SqlConnection myCon = new SqlConnection(conStr);
            SqlCommand myCmd = new SqlCommand(strSql, myCon);
            DataSet myDataSet = new DataSet();
            try
            {
                myCon.Open();
                System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter(strSql, myCon);
                myAdapter.Fill(myDataSet, strSql);
                errorCode = 1;
            }
            catch (Exception e)
            {
                errorCode = 0;
                return null;
            }
            return myDataSet;
        }

        /**
         * Des: Kiem tra xem gia tri val co ton tai trong cot colname cua bang tablename hay khong.
         *      Neu ton tai ham tra ve true, nguoc lai tra ve false.
         *      ErrorCode duoc gan bang 1 neu viec kiem tra thanh cong, nguoc lai errorCode se co
         *      gia tri bang 0.
         */
        public static bool checkValue(string val, string colname, string tablename, out int errorCode)
        {
            string strSql = "select " + colname
                + " from " + tablename
                + " where " + colname + "='"
                + val.Replace("'", "''") + "';";

            if (executeQuery(strSql, out errorCode) > 0 && errorCode == 1) return true;
            return false;
        }

        /**
         * Des: This is a compile with checkValue and executeQuery. 
         *      Replace later.
         */
        public static bool checkExist(string val, string colname, string tablename, out int errorCode)
        {
            string strSql = "select " + colname
                + " from " + tablename
                + " where " + colname + "=N'"
                + val.Replace("'", "''") + "';";

            string myConStr = ConfigurationSettings.AppSettings["connectionstring"];
            SqlConnection myCon = new SqlConnection(myConStr);
            SqlCommand myCmd = new SqlCommand(strSql, myCon);
            SqlDataReader drd;
            try
            {
                myCon.Open();
                errorCode = 1;
                drd = myCmd.ExecuteReader();
                if (drd.HasRows == true) return true;
            }
            catch (Exception e)
            {
                errorCode = 0;
            }
            finally
            {
                myCmd.Connection.Close();
                myCon.Close();
            }
            return false;
        }

        /**
         * Des: This is a compile with checkValue and executeQuery. 
         *      Replace later.
         */
        public static bool checkExist(int val, string colname, string tablename, out int errorCode)
        {
            string strSql = "select " + colname
                + " from " + tablename
                + " where " + colname + "=" + val + ";";

            string myConStr = ConfigurationSettings.AppSettings["connectionstring"];
            SqlConnection myCon = new SqlConnection(myConStr);
            SqlCommand myCmd = new SqlCommand(strSql, myCon);
            SqlDataReader drd;
            try
            {
                myCon.Open();
                errorCode = 1;
                drd = myCmd.ExecuteReader();
                if (drd.HasRows == true) return true;
            }
            catch (Exception e)
            {
                errorCode = 0;
            }
            finally
            {
                myCmd.Connection.Close();
                myCon.Close();
            }
            return false;
        }

         

        // valList: separate by comma
        public static bool deleteAll(string valList, string colname, string tablename)
        {
            string strSql = "delete " + tablename
                + " where " + colname + " in ('" + valList.Replace("'", "''").Replace(",", "','") + "')";
            return ExecuteNonQuery(strSql, getConnectionString());
        }

        public static bool deleteAll(ArrayList valList, string colname, string tablename)
        {
            // make list
            string list = "";
            for (int i = 0; i < valList.Count; i++)
                list += "," + valList[i];
            if (!list.Equals(""))
            {
                list = list.Substring(1);
                string strSql = "delete " + tablename
                    + " where " + colname + " in ('" + list.Replace("'", "''").Replace(",", "','") + "')";
                return ExecuteNonQuery(strSql, getConnectionString());
            }
            return false;
        }

        public static bool deleteAllData(string tablename, string strFilter)
        {
            string strSql = "delete from " + tablename + (strFilter.Equals("") ? "" : " WHERE " + strFilter);
            return ExecuteNonQuery(strSql, getConnectionString());
        }

        public static String GetName(String idName, String idValue, String name, String tableName)
        {
            int err = 0;
            String sqlStr = "Select " + name + " From " + tableName + " Where " + idName + "='" + idValue + "'";
            SqlDataReader drd = Helper.executeQuery(sqlStr, Helper.getConnectionString(), out err);
            if (drd != null && drd.Read())
            {
                return (drd["" + name] == DBNull.Value ? "" : (String)drd["" + name]);
            }
            return "";
        }
        public static string getFilename(System.Web.UI.HtmlControls.HtmlInputFile ctrlname)
        {
            string s1;
            int pos;
            s1 = ctrlname.PostedFile.FileName;
            if (s1 == "")
                return "";
            pos = s1.LastIndexOf("\\") + 1;
            string sChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
            int lenRandom = 20; //Random 1 chuoi voi len = 10
            Random rd = new Random(unchecked((int)DateTime.Now.Ticks));
            string fileName = string.Empty;
            for (int i = 0; i < lenRandom; i++)
                fileName += sChars[rd.Next(0, sChars.Length - 1)].ToString();

            s1 = s1.Substring(pos);
            s1 = s1.Replace(s1.Substring(0, s1.IndexOf(".")), fileName);
            return s1.Substring(pos);
        }
        public static void upload(System.Web.UI.HtmlControls.HtmlInputFile ctrlname, string dir_from_root, string filename, System.Web.HttpRequest request)
        {
            string path = request.ServerVariables["APPL_PHYSICAL_PATH"];
            path = path + dir_from_root + filename;
            ctrlname.PostedFile.SaveAs(path);
        }

    }
}